﻿using MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL
{
    public class TruckDAL
    {
        #region 多条语句查询,车辆信息
        /// <summary>
        /// 多条语句查询,车辆信息
        /// </summary>
        /// <param name="querymodel"></param>
        /// <returns></returns>
        public List<TruckMODEL> GetTruckList(TruckTeamQueryModel querymodel)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(@"select  k.FK_TeamID,k.TruckID,k.Number,k.BuyDate,k.Type,k.Length,k.Tonngae,m.TeamName,m.Remark,
        case k.State
        when 1 then '承运中'
        else '空闲中' end as State,
        case k.IsDelete
        when 0 then '否'
        else '是' end as IsDelete,m.TeamID,
        m.CheckInTime,m.AlterTime from dbo.[Truck] as k inner join dbo.TruckTeam as m on k.FK_TeamID=m.TeamID");

            List<string> listwhere = new List<string>();
            List<SqlParameter> listparm = new List<SqlParameter>();
            //所属车队
            if (querymodel.FK_TeamID != 0)
            {
                listwhere.Add("k.FK_TeamID=@FK_TeamID");
                listparm.Add(new SqlParameter("@FK_TeamID", querymodel.FK_TeamID));
            }
            //车牌号码
            if (!string.IsNullOrWhiteSpace(querymodel.Number))
            {
                listwhere.Add("k.Number=@Number");
                listparm.Add(new SqlParameter("@Number", querymodel.Number));
            }
            //车辆类型
            if (querymodel.Type != "--全部--")
            {
                listwhere.Add("k.Type=@Type");
                listparm.Add(new SqlParameter("@Type", querymodel.Type));
            }
            //车辆状态
            if (querymodel.State >= 0)
            {
                listwhere.Add("k.State=@State");
                listparm.Add(new SqlParameter("@State", querymodel.State));
            }
            //是否删除
            if (querymodel.IsDelete >= 0)
            {
                listwhere.Add("k.IsDelete=@IsDelete");
                listparm.Add(new SqlParameter("@IsDelete", querymodel.IsDelete));
            }
            //判断是否有条件，连接字段
            if (listwhere.Count > 0)
            {
                builder.Append($" where {string.Join(" and ", listwhere.ToArray())}");
            }
            DataTable table = DBhelper.SqlDataAdapter(builder.ToString(), listparm.ToArray());

            List<TruckMODEL> list = new List<TruckMODEL>();
            foreach (DataRow row in table.Rows)
            {
                TruckMODEL model = new TruckMODEL();
                model.TruckID = Convert.ToInt32(row["TruckID"]);//车辆id
                model.FK_TeamID = Convert.ToInt32(row["FK_TeamID"]);//车队id
                model.BuyDate = Convert.ToDateTime(row["BuyDate"]);//购车时间
                model.TeamName = row["TeamName"].ToString();//车队名称
                model.Number = row["Number"].ToString();//车牌号码
                model.Type = row["Type"].ToString();//车辆类型
                model.Length = row["Length"].ToString();//车身长度
                model.Tonngae = Convert.ToInt32(row["Tonngae"].ToString());//车辆吨位
                model.Remark = row["Remark"].ToString();//备注
                model.CheckInTime = Convert.ToDateTime(row["CheckInTime"]);//添加时间
                model.AlterTime = Convert.ToDateTime(row["AlterTime"]);//修改时间
                model.State = row["State"].ToString();//车辆状态
                model.IsDelete = row["IsDelete"].ToString();//是否删除
                list.Add(model);
            }
            return list;
        }
        #endregion

        #region 删除信息，车辆信息进行修改式删除
        /// <summary>
        /// 删除信息，车辆信息进行修改式删除
        /// </summary>
        /// <param name="delete"></param>
        /// <returns></returns>
        public int DeleteTruckList(int id)
        {
            //string sql = "delete from  dbo.[Truck] where TruckID=@TruckID";
            string sql = @"update dbo.[Truck] set IsDelete=1 where TruckID=@TruckID";
            SqlParameter[] parm = {
                new SqlParameter("@TruckID",id)
            };
            return DBhelper.ExecuteNonQuery(sql, parm);
        }
        #endregion

        #region 删除车辆信息
        /// <summary>
        /// 删除车辆信息根据id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public TruckUpdateModel GetDeleteTruckByID(int id)
        {
            string sql = @"select * from dbo.[Truck] where TruckID=@TruckID";
            SqlParameter[] parm = {
                    new SqlParameter("@TruckID",id)
                };

            //连线式查询，调用DBhelper类中方法
            using (SqlDataReader reader = DBhelper.ExecuteReader(sql, parm))
            {
                //如果游标有数据行，说明根据账户名和密码查询到了数据，登录成功
                if (reader.HasRows)
                {
                    //登录成功后，执行下一条记录，获取登录用户的数据
                    if (reader.Read())
                    {
                        TruckUpdateModel team = new TruckUpdateModel();
                        //车辆编号
                        team.TruckID = Convert.ToInt32(reader["TruckID"]);
                        //车牌号码
                        team.Number = reader["Number"].ToString();
                        //购车时间
                        team.BuyDate = Convert.ToDateTime(reader["BuyDate"]);
                        //车辆类型
                        team.Type = reader["Type"].ToString();
                        //车队编号
                        team.FK_TeamID = Convert.ToInt32(reader["FK_TeamID"]);
                        //车身长度
                        team.Length = reader["Length"].ToString();
                        //吨位
                        team.Tonngae = Convert.ToInt32(reader["Tonngae"]);
                        //工作状态
                        team.State = reader["State"].ToString();
                        //备注
                        team.Remark = reader["Remark"].ToString();
                        //是否删除
                        team.IsDelete = Convert.ToBoolean(reader["IsDelete"]);
                        //添加时间
                        team.CheckInTime = Convert.ToDateTime(reader["CheckInTime"]);
                        //修改时间
                        team.AlterTime = Convert.ToDateTime(reader["AlterTime"]);
                        return team;
                    }
                }
                return null;
            }
        }
        #endregion

        #region 添加车辆信息
        /// <summary>
        /// 添加车辆信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int UInsertTruck(TruckTeamQueryModel sql)
        {
            string inser = @"INSERT INTO dbo.Truck
        ( FK_TeamID ,Number ,BuyDate ,Type ,Length ,Tonngae ,State ,Remark ,CheckInTime ,IsDelete ,AlterTime)
VALUES  ( @FK_TeamID, @Number , @BuyDate ,@Type ,@Length ,@Tonngae ,
@State ,@Remark ,@CheckInTime ,@IsDelete ,@AlterTime)";
            SqlParameter[] parm ={
                new SqlParameter("@FK_TeamID",sql.FK_TeamID),
                new SqlParameter("@Number",sql.Number),
                new SqlParameter("@BuyDate",sql.BuyDate),
                new SqlParameter("@Type",sql.Type),
                new SqlParameter("@Length",sql.Length),
                new SqlParameter("@Tonngae",sql.Tonngae),
                 new SqlParameter("@State",sql.State),
                new SqlParameter("@Remark",sql.Remark),
                new SqlParameter("@CheckInTime",DateTime.Now),
                new SqlParameter("@IsDelete",false),
                new SqlParameter("@AlterTime",DateTime.Now)
            };
            return DBhelper.ExecuteNonQuery(inser, parm);
        }
        #endregion

        #region 修改车辆信息
        /// <summary>
        /// 修改车辆信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateTruck(TruckMODEL model)
        {
            string update = @"update truck set Number=@Number,BuyDate=@BuyDate,Type=@Type,Length=@Length,Tonngae=@Tonngae,
State=@State,AlterTime=@AlterTime where TruckID=@TruckID";

            SqlParameter[] parm = {
                new SqlParameter("@Number",model.Number),
                new SqlParameter("@BuyDate",model.BuyDate),
                new SqlParameter("@Type",model.Type),
                new SqlParameter("@Length",model.Length),
                new SqlParameter("@Tonngae",model.Tonngae),
                new SqlParameter("@State",model.State),
                new SqlParameter("@AlterTime",model.AlterTime),
                new SqlParameter("@TruckID",model.TruckID),

            };
            return DBhelper.ExecuteNonQuery(update, parm);
        }
        #endregion

        #region 根据id进行车辆信息的修改
        /// <summary>
        /// 根据ID获取编辑车队信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public TruckMODEL GetUpdateTruckID(int teamid)
        {
            string sql = @"SELECT * FROM dbo.Truck WHERE TruckID=@TruckID";
            //参数
            SqlParameter[] pams = {
                new SqlParameter("@TruckID",teamid)

            };

            //连线式查询，调用DBhelper类中方法
            using (SqlDataReader reader = DBhelper.ExecuteReader(sql, pams))
            {
                //如果游标有数据行，说明根据账户名和密码查询到了数据，登录成功
                if (reader.HasRows)
                {
                    //登录成功后，执行下一条记录，获取登录用户的数据
                    if (reader.Read())
                    {
                        TruckMODEL user = new TruckMODEL();
                        //车辆编号
                        user.TruckID = Convert.ToInt32(reader["TruckID"]);
                        //车辆号码
                        user.Number = reader["Number"].ToString();
                        //购车时间
                        user.BuyDate = Convert.ToDateTime(reader["BuyDate"]);
                        //车辆状态
                        user.State = reader["State"].ToString();
                        //备注
                        user.Remark = reader["Remark"].ToString();
                        //车辆类型
                        user.Type = reader["Type"].ToString();
                        //车身长度
                        user.Length = reader["Length"].ToString();
                        //吨位
                        user.Tonngae = Convert.ToInt32(reader["Tonngae"]);
                        //车队编号
                        user.FK_TeamID = Convert.ToInt32(reader["FK_TeamID"]);
                        return user;
                    }
                }
                return null;
            }
        }
        #endregion

        #region 分页存储过程，车辆信息
        /// <summary>
        /// 分页存储过程，车辆信息
        /// </summary>
        /// <param name="number"></param>
        /// <param name="type"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCout"></param>
        /// <returns></returns>
        public List<TruckMODEL> GetPagerTruckList(string number, int pageIndex, int pageSize, out int totalCout)
        {
            // string type, 
            totalCout = 0;
            List<TruckMODEL> list = new List<TruckMODEL>();
            string procName = "Proc_GetPagedTruckList";
            SqlParameter[] parm = {
                new SqlParameter("@Number",number),
                new SqlParameter("@PageIndex",pageIndex),
                new SqlParameter("@PageSize",pageSize),
                new SqlParameter("@TotalCount",totalCout)
            };
            parm[3].Direction = ParameterDirection.Output;

            SqlDataReader reader = DBhelper.ExecuteReaderProc(procName, parm);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    TruckMODEL model = new TruckMODEL();
                    model.TruckID = Convert.ToInt32(reader["TruckID"]);
                    model.FK_TeamID = Convert.ToInt32(reader["FK_TeamID"]);
                    model.BuyDate = Convert.ToDateTime(reader["BuyDate"]);
                    model.Number = reader["Number"].ToString();
                    model.Type = reader["Type"].ToString();
                    model.Length = reader["Length"].ToString();
                    model.Tonngae = Convert.ToInt32(reader["Tonngae"].ToString());
                    model.Remark = reader["Remark"].ToString();
                    model.CheckInTime = Convert.ToDateTime(reader["CheckInTime"]);
                    model.AlterTime = Convert.ToDateTime(reader["AlterTime"]);
                    model.State = reader["State"].ToString();
                    model.IsDelete = reader["IsDelete"].ToString();
                    list.Add(model);
                }
                reader.Close();
                totalCout = Convert.ToInt32(parm[3].Value);
            }
            return list;
        } 
        #endregion
    }
}
